package cn.edu.sdjzu.xg2106.dao;

import cn.edu.sdjzu.xg2106.domain.University;
import cn.edu.sdjzu.xg2106.util.Condition;
import cn.edu.sdjzu.xg2106.util.JdbcHelper;
import cn.edu.sdjzu.xg2106.util.Pagination;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.HashSet;
import java.util.List;

public class UniversityDao {
    private static UniversityDao universityDao = new UniversityDao();
    //阻止在本类外生成本类的对象
    private UniversityDao(){
    }

    public static UniversityDao getInstance(){
        return universityDao;
    }
    public University find(int id) throws SQLException {
        University desiredUniversity = null;
        Connection connection= JdbcHelper.getConn();
        String sql_select_id="select * from university where id=?";
        PreparedStatement pstmt_select_id=connection.prepareStatement(sql_select_id);
        pstmt_select_id.setInt(1,id);
        ResultSet resultSet_select_id=pstmt_select_id.executeQuery();
        if (resultSet_select_id.next()){
            String description = resultSet_select_id.getString("description");
            String no = resultSet_select_id.getString("no");
            String remarks = resultSet_select_id.getString("remarks");
            desiredUniversity = new University(id, description, no,remarks);
        }
        JdbcHelper.close(resultSet_select_id,pstmt_select_id,connection);
        return desiredUniversity;
    }
    public Collection<University> findAll(Connection connection, List<Condition> conditionList, Pagination pagination) throws SQLException {
        //创建集合类对象
        Collection<University> desiredUniversity = new HashSet<University>();
        //创建查询教师的主语句
        StringBuilder sql_select = new StringBuilder("SELECT * FROM university");
        if (conditionList != null) {
            String whereClause = Condition.toWhereClause(conditionList);
            sql_select.append(whereClause);
        }
        int totalNum = CommonDao.getInstance().count(connection, "university", conditionList);
        if (pagination != null) {
            String paginationClause = pagination.toLimitClause(totalNum);
            sql_select.append(paginationClause);
        }
        PreparedStatement pstmt_select_all = (connection).prepareStatement(sql_select.toString());
        ResultSet resultSet = pstmt_select_all.executeQuery();
        while (resultSet.next()) {
            int id = resultSet.getInt("id");
            String description = resultSet.getString("description");
            String no = resultSet.getString("no");
            String remarks = resultSet.getString("remarks");
            University desiredUniversities = new University(id, description, no,remarks);
            desiredUniversity.add(desiredUniversities);
        }
        JdbcHelper.close(resultSet, pstmt_select_all);
        return desiredUniversity;
    }

    public boolean add(Connection connection,University university)throws SQLException{
        PreparedStatement preparedStatement=connection.prepareStatement(
                "insert into university"+
                        "(description,no,remarks)"+
                        "values(?,?,?)");
        preparedStatement.setString(2,university.getNo());
        preparedStatement.setString(1,university.getDescription());
        preparedStatement.setString(3,university.getRemarks());
        int rowAffected = preparedStatement.executeUpdate();
        return rowAffected>0;
    }

    public boolean update(Connection connection,University university)throws SQLException{
//update 表名 set 列名1=值1，列名2=值2;
        PreparedStatement preparedStatement=connection.prepareStatement(
                "update university set description =?,no =?,remarks=? where id=? "
        );
        preparedStatement.setString(1,university.getDescription());
        preparedStatement.setString(2,university.getNo());
        preparedStatement.setString(3,university.getRemarks());
        preparedStatement.setLong(4,university.getId());
        int rowAffected = preparedStatement.executeUpdate();
        return rowAffected>0;
    }
    public void delete(long id) throws SQLException{
        Connection connection = JdbcHelper.getConn();
        PreparedStatement preparedStatement =
                connection.prepareStatement("delete from university"+" where id =?");
        preparedStatement.setLong(1,id);
        preparedStatement.executeQuery();
        JdbcHelper.close(preparedStatement,connection);
    }
}
